USE [JaxDug]
GO

SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER ON
GO

IF EXISTS (SELECT [name] FROM sys.objects WHERE object_id = OBJECT_ID(N'dbo.GetNextPresentations') AND TYPE IN (N'P', N'PC'))
BEGIN
	DROP PROC GetNextPresentations
	PRINT 'DROP PROC GetNextPresentations'
END
GO

-- ===============================================================
-- Author:		RbH
-- Create date: 2012.02.29
-- Description:	Selects basic info about upcoming Presentations
-- Ussage:		EXEC GetNextPresentations 3
-- ===============================================================
CREATE PROCEDURE dbo.GetNextPresentations
	@Count INT
AS
BEGIN
	SET NOCOUNT ON;

	DECLARE @PresentationIDs TABLE ( PresentationID INT )
	
	INSERT INTO @PresentationIDs ( PresentationID )
	SELECT TOP (@Count) p.PresentationID
	FROM dbo.Presentations p
	WHERE p.StartDateTime >= CAST(GETDATE() AS DATE)
		
    SELECT
		p.PresentationID, p.Title, p.StartDateTime, p.EndDateTime,
		u.UserID AS 'SpeakerID', u.FirstName, u.LastName
	FROM dbo.Presentations p
		INNER JOIN @PresentationIDs tv ON p.PresentationID = tv.PresentationID
		INNER JOIN dbo.Users u ON u.UserID = p.SpeakerID
	ORDER BY p.PresentationID ASC
	
	SELECT DISTINCT pt.PresentationID, t.TagID, t.Tag
	FROM dbo.Tags t
		INNER JOIN dbo.PresentationTags pt ON t.TagID = pt.TagID
		INNER JOIN @PresentationIDs tv ON pt.PresentationID = tv.PresentationID
END
GO

PRINT 'CREATE PROC GetNextPresentations'

GRANT EXEC ON GetNextPresentations TO PublicDbUser
GO

PRINT 'GRANT EXEC ON GetNextPresentations TO PublicDbUser'